package com.source.chart.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.source.chart.VO.*;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public interface PoultrySalesMapper extends BaseMapper<PoultrySalesVO> {


    @Select("SELECT " +
            "any_value(poultry_category.poultryCategoryId) AS `poultryId`," +
            "any_value(labeling_record.LabelingDate) AS labelingDate," +
            "any_value(labeling_record.barcodeMachineNumber) AS barcodeMachineNumber, " +
            "any_value(labeling_record.barcodeNumber) AS barcodeNumber, " +
            "poultry_category.poultryCategoryName AS `poultryName`, " +
            "WEEK(labeling_record.LabelingDate) as `week`," +
            "COUNT(poultry_category.poultryCategoryName) AS `salesSum`," +
            "slaughter_house.address AS `houseAddr`," +
            "slaughter_house.slaughterHouseName AS `houseName`," +
            "slaughter_house.latitude AS `houseLatitude`," +
            "slaughter_house.longitude AS `houseLongitude` " +
            "FROM labeling_record " +
            "LEFT JOIN poultry_category ON labeling_record.poultryCategoryId=poultry_category.poultryCategoryId " +
            "LEFT JOIN slaughter_house ON poultry_category.slaughterHouseId=slaughter_house.slaughterHouseId " +
            "WHERE poultry_category.slaughterHouseId=#{house_id} GROUP BY `week`, poultryName")
    List<PoultrySalesVO> findAllSalesByHouse(int house_id);

    @Select(
            value = "SELECT poultryCategoryId from poultry_category " +
                    "WHERE parentId= #{parentId} AND slaughterHouseId= #{houseId}"
    )
    List<String> findIdByParentAndHouseId(String parentId, String houseId);

    @Select(
            value = "SELECT COUNT(labelingRecordId),YEAR(labelingDate) AS `year` " +
                    "FROM labeling_record " +
                    "WHERE poultryCategoryId IN  (#{idList}) " +
                    "GROUP BY `year` " +
                    "ORDER BY `year` ASC"
    )
    List<Object> findCountOfPoultryInYear(List<String> idList);

//
//    @Select("SELECT poultryParentName, COUNT(*) count  FROM `poultry_category` pc, slaughter_house sh, poultry_category_parent pcp\n" +
//            "        WHERE sh.slaughterHouseId = pc.slaughterHouseId AND pc.parentId = pcp.poultryCategoryParentId\n" +
//            "          AND sh.isDelete = 0 AND pc.isDelete = 0 AND pcp.isDelete = 0 AND sh.slaughterHouseId = #{houseId}\n" +
//            "        GROUP BY poultryParentName")
//    List<PoultrySales> findCountOfParentInYearByHouseId(String houseId);

    /**
     * findCountOfParentByHouseId 根据屠宰场查询，再种类分类
     * @param houseId
     * @return
     */
    @Select("SELECT  COUNT(*) count ,pc.parentId\n" +
            "                    FROM `poultry_category` pc, slaughter_house sh, poultry_category_parent pcp\n" +
            "                    WHERE sh.slaughterHouseId = pc.slaughterHouseId AND pc.parentId = pcp.poultryCategoryParentId\n" +
            "                    AND sh.isDelete = 0 AND pc.isDelete = 0 AND pcp.isDelete = 0 AND sh.slaughterHouseId = '${houseId}' \n" +
            "                    GROUP BY pc.parentId ")
    List<PoultryParentCountVo> findCountOfParentByHouseId(@Param("houseId") String houseId);

    @Select("select poultryCategoryParentId,poultryParentName \n" +
            "from poultry_category_parent as pcp where pcp.isDelete=0 \n" +
            "GROUP BY pcp.poultryCategoryParentId")
    List<PoultryParentMapVo> selectPoultryParentMapVo();

    @Select("select c.poultryParentName,COUNT(*) as count \n" +
            "from labeling_record  as a,poultry_category as b,poultry_category_parent as c\n" +
            "where a.isDelete=0\n" +
            "and b.isDelete=0\n" +
            "and c.isDelete=0\n" +
            "and a.poultryCategoryId=b.poultryCategoryId\n" +
            "and b.parentId=c.poultryCategoryParentId\n" +
            "and YEAR(a.labelingDate)=YEAR(NOW())\n" +
            "and b.slaughterHouseId='${houseId}'\n" +
            "GROUP BY c.poultryParentName ")
    List<PoultrySpeciesCountChart> selectPoultryCategoryCountOfYear(@Param("houseId") String houseId);

    @Select("select c.poultryParentName,COUNT(*) as count \n" +
            "from labeling_record  as a,poultry_category as b,poultry_category_parent as c\n" +
            "where a.isDelete=0\n" +
            "and b.isDelete=0\n" +
            "and c.isDelete=0\n" +
            "and a.poultryCategoryId=b.poultryCategoryId\n" +
            "and b.parentId=c.poultryCategoryParentId\n" +
            "and b.slaughterHouseId='${houseId}'\n" +
            "GROUP BY c.poultryParentName ")
    List<PoultrySpeciesCountChart> selectPoultryCategoryCountOfAll(@Param("houseId")String houseId);

    @Select("select c.poultryParentName,COUNT(*)\n" +
            "from labeling_record  as a,poultry_category as b,poultry_category_parent as c\n" +
            "where a.isDelete=0\n" +
            "and b.isDelete=0\n" +
            "and c.isDelete=0\n" +
            "and a.poultryCategoryId=b.poultryCategoryId\n" +
            "and b.parentId=c.poultryCategoryParentId\n" +
            "and DATE_FORMAT(a.labelingDate,'%Y%m')=DATE_FORMAT( '${date}', '%Y%m' )\n" +
            "and b.slaughterHouseId='${houseId}'\n" +
            "GROUP BY c.poultryParentName")
    List<PoultrySpeciesCountChart> selectPoultryCateCountOfMoth(@Param("houseId")String houseId,@Param("date") String date);

}
